-- 为承办人分配证书申请人角色 分租户执行
<#list tenant_ids as tenant_id>
insert into cms.fnd_user_company_role
(
    ID,
    TENANT_ID,
    USER_ID,
    COMPANY_UUID,
    GROUP_ID,
    ROLE_ID,
    ENABLED,
    CREATE_DATE,
    CREATED_BY,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    EFFECTIVE_START_TIME,
    EFFECTIVE_END_TIME,
    UPLOAD_DATE
)
select
    sys_guid(),
    fucr.TENANT_ID,
    fucr.USER_ID,
    fucr.COMPANY_UUID,
    fucr.GROUP_ID,
    fr2.id,
    'Y',
    SYSDATE,
    fucr.CREATED_BY,
    fucr.LAST_UPDATED_BY,
    SYSDATE,
    SYSDATE,
    SYSDATE,
    SYSDATE
from cms.fnd_user_company_role fucr
left join cms.fnd_role fr on fucr.role_id = fr.id
inner join cms.fnd_role fr2 on fr2.code = 'ZSSQR'
where fr.code = 'CBR'
    and fr.ENABLED = 'Y'
    and fucr.ENABLED = 'Y'
    and fucr.TENANT_ID = '${tenant_id}'
    and not exists(
        select
            1
        from cms.fnd_user_company_role fucr2
        where fucr2.TENANT_ID = fucr.TENANT_ID
        and fucr2.ROLE_ID = fr2.ID
    );

</#list>